package br.com.crednove.modelo;

import br.com.crednove.controle.ControleImportar;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

public class Access {

    private final String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
    private final String user = "";
    private final String senha = "";
    private String nomeBanco = "";
    private String url = "jdbc:odbc:";
    private Connection conector;
    private ArrayList<String> tabelas;
    private ArrayList<String> colunas;
    private ResultSet rs;
    private Cliente cliente;

    public Access(String nomeBanco) {
        this.nomeBanco = nomeBanco;
        this.url = this.url + this.nomeBanco;
        this.colunas = new ArrayList<>();
        this.tabelas = new ArrayList<>();
        this.cliente = new Cliente();
        this.rs = null;
    }

    public boolean conectar() {
        try {
            Class.forName(this.driver);
            this.conector = DriverManager.getConnection(this.url, this.user, this.senha);
            return true;
        } catch (ClassNotFoundException | SQLException ex) {
            System.out.println(ex);
            return false;
        }
    }

    public String[] importarTabelas() {
        this.tabelas.clear();
        try {
            DatabaseMetaData md = conector.getMetaData();
            ResultSet rs = md.getTables(null, null, null, new String[]{"TABLE"});
            while (rs.next()) {
                tabelas.add(rs.getString("TABLE_NAME"));
                //System.out.println(rs.getString("TABLE_NAME"));
            }
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(Access.class.getName()).log(Level.SEVERE, null, ex);
        }
        return tabelas.toArray(new String[tabelas.size()]);
    }

    public String[] importarColunas(String tabela) {
        PreparedStatement pstm = null;
        this.colunas.clear();
        try {
            System.out.println("select * from ".concat(tabela).concat(" where 1=0"));
            pstm = conector.prepareStatement("select * from ".concat(tabela).concat(" where 1=0"));
            ResultSet rs = pstm.executeQuery();
            ResultSetMetaData rsmdt = rs.getMetaData();
            for (int i = 0; i < rsmdt.getColumnCount(); i++) {
                this.colunas.add(rsmdt.getColumnName(i + 1));
                //System.out.println(rsmdt.getColumnName(i+1));
            }
        } catch (SQLException ex) {
            Logger.getLogger(Access.class.getName()).log(Level.SEVERE, null, ex);
        }
        return colunas.toArray(new String[colunas.size()]);
    }

    public long totalRegistros() {
        long total = 0;
        try {
            this.rs.last();
            total = this.rs.getRow();
            this.rs.beforeFirst();
        } catch (SQLException ex) {
            Logger.getLogger(Access.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            return total;
        }
    }

    public int totalColunas() {
        int total = 0;
        try {
            ResultSetMetaData rsmdt = rs.getMetaData();
            total = rsmdt.getColumnCount();
        } catch (SQLException ex) {
            Logger.getLogger(Access.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            return total;
        }
    }

    public boolean carregarClientes(String tabela) {
        boolean retorno = false;

        Statement pstm = null;
        this.colunas.clear();
        try {
            final String sql = "select * from ".concat(tabela);
            pstm = conector.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            this.rs = pstm.executeQuery(sql);
            retorno = true;
        } catch (SQLException ex) {
            Logger.getLogger(Access.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            return retorno;
        }
    }

    public Cliente importarCliente(int campos[]) {
        PreparedStatement pstm = null;
        try {
            this.cliente.limparInformacoes();
            for (int i = 0; i < campos.length; i++) {
                if (campos[i] != ControleImportar.SEM_VINCULO) {
                    String valor = this.rs.getString(campos[i]+1);
                    this.cliente.preencheCampo(i, valor);
                }
            }
        } catch (SQLException ex) {
            System.out.println(ex);
        } finally {
            return cliente;
        }
    }

    public void desconectar() {
        try {
            this.conector.close();
        } catch (SQLException ex) {
            System.out.println(ex);
        }
    }

    public Connection getConector() {
        return conector;
    }

    public ArrayList<String> getTabelas() {
        return tabelas;
    }

    public ResultSet getRs() {
        return rs;
    }
}
